# pip3 install mysql-connector-python
import socket
import mysql.connector  # Changed from sqlite3 to mysql.connector
import signal
import sys
from datetime import datetime

# Debug flag
DEBUG = True  # Set to True to disable MySQL saving and print data instead

# Database setup
def init_db():
    conn = mysql.connector.connect(
        host="localhost",  # Update with your MySQL host
        user="your_username",  # Update with your MySQL username
        password="your_password",  # Update with your MySQL password
        database="your_database"  # Update with your MySQL database name
    )
    c = conn.cursor()
    c.execute('''CREATE TABLE IF NOT EXISTS device_data (
                 id INT AUTO_INCREMENT PRIMARY KEY,
                 timestamp VARCHAR(255) NOT NULL,
                 data BLOB NOT NULL)''')  # Changed data type to BLOB
    conn.commit()
    return conn

# TCP server
def signal_handler(sig, frame):
    print("\nShutting down server...")
    if 'db_conn' in signal_handler.__globals__:
        signal_handler.db_conn.close()
        print("Database connection closed")
    sys.exit(0)

def start_server(host='0.0.0.0', port=55555):
    # Register signal handler
    signal.signal(signal.SIGINT, signal_handler)
    
    # Initialize database and store connection globally if not in debug mode
    if not DEBUG:
        signal_handler.db_conn = init_db()
    
    # Create TCP socket
    try:
        with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as server_socket:
            server_socket.bind((host, port))
            server_socket.listen(5)
            server_socket.settimeout(1)  # Allow periodic check for SIGINT
            print(f"Server listening on {host}:{port}")

            while True:
                try:
                    # Accept client connection
                    client_socket, addr = server_socket.accept()
                    print(f"Connection from {addr}")

                    with client_socket:
                        try:
                            # Receive data
                            raw_data = client_socket.recv(1024)
                            if not raw_data:
                                continue
                            
                            # Log raw data as hex string for debugging
                            data_hex = raw_data.hex()
                            print(f"Received data (hex): {data_hex}")
                            
                            # Extract dtu_id and dtu_data
                            if b',' in raw_data:
                                dtu_id, dtu_data = raw_data.split(b',', 1)
                            else:
                                dtu_id, dtu_data = raw_data, b""
                            
                            print(f"Processed data: dtu_id={dtu_id.decode(errors='ignore')}, dtu_data={dtu_data.hex()}")
                            
                            if DEBUG:
                                # Debug mode: Print data instead of saving
                                print(f"DEBUG is True, data not saved: dtu_id={dtu_id.decode(errors='ignore')}, dtu_data={dtu_data.hex()}")
                            else:
                                # Save to database
                                timestamp = datetime.now().isoformat()
                                c = signal_handler.db_conn.cursor()
                                try:
                                    c.execute("INSERT INTO device_data (timestamp, data) VALUES (%s, %s)",
                                             (timestamp, raw_data))  # Save raw binary data
                                    signal_handler.db_conn.commit()
                                    print(f"Data saved: dtu_id={dtu_id.decode(errors='ignore')}, dtu_data={dtu_data.hex()}")
                                except mysql.connector.Error as e:
                                    print(f"Failed to save data: {e}")
                            
                            # Send acknowledgment
                            # client_socket.sendall(b"Data received and processed")
                            
                        except Exception as e:
                            print(f"Error handling client {addr}: {e}")
                            # client_socket.sendall(b"Error processing request")
                except socket.timeout:
                    # Timeout is expected, continue listening
                    continue
                except Exception as e:
                    print(f"Server error: {e}")
                    break

    finally:
        print("Server socket closed")
        if not DEBUG and 'db_conn' in signal_handler.__globals__:
            signal_handler.db_conn.close()
            print("Database connection closed")

if __name__ == "__main__":
    start_server()
